package 地区数据;
import Moudle.Excel_地区数据;
import Util.Tool;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.Collator;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

public class SortExcel {

    static Logger logger = LoggerFactory.getLogger(SortExcel.class);

    public static void main(String[] args) {
        List<String> file_path_list = Tool.GetAllFilePath("D:\\测试临时文件\\国家统计局\\数据");
        List<String> ERROR_LIST = new ArrayList<>();
        for (String file_path : file_path_list){
            FIRST_LINE first_line = new FIRST_LINE();
            List<Excel_地区数据> excel_list = new ArrayList<>();
            try{
                GetDataFromExcel(file_path, first_line, excel_list);
                SortExcelData(excel_list);
                WriteToFile(file_path, first_line, excel_list);
            }catch (Exception e){
                e.printStackTrace();
                ERROR_LIST.add(file_path);
            }

        }

        System.out.println("\n-------------------error_list-------------------");
        for (String error_file_path : ERROR_LIST){
            System.out.println(error_file_path);
        }
    }

    static void WriteToFile(String excel_path, FIRST_LINE first_line, List<Excel_地区数据> excel_list) throws IOException {
        int index = excel_path.lastIndexOf("数据\\")+3;
        String file_name = excel_path.substring(index);

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        Row first_row = sheet.createRow(0);
        first_row.createCell(0).setCellValue(first_line.A1);
        first_row.createCell(1).setCellValue(first_line.B1);
        int n = first_line.others.size();
        for (int i = 0; i < n ; i++) {
            first_row.createCell(i+2).setCellValue(first_line.others.get(i));
        }

        index = 1;
        for (Excel_地区数据 excel_item : excel_list){
            Row row = sheet.createRow(index++);
            row.createCell(0).setCellValue(excel_item.City_name);
            row.createCell(1).setCellValue(excel_item.Time);
            for (int i = 0; i < n; i++) {
                row.createCell(i+2).setCellValue(excel_item.list.get(i));
            }
        }

        String file_path = "D:\\测试临时文件\\国家统计局\\排序后\\" + file_name;
        workbook.write(new FileOutputStream(file_path));
        logger.info(file_name + " 写入完成");
    }

    static void SortExcelData(List<Excel_地区数据> excel_list){
        Collections.sort(excel_list, new Comparator<Excel_地区数据>() {
            @Override
            public int compare(Excel_地区数据 o1, Excel_地区数据 o2) {

                Comparator comparator = Collator.getInstance(java.util.Locale.CHINA);

                int cmp = 0;

                cmp = comparator.compare(o1.City_name, o2.City_name);
                if (cmp != 0)
                    return cmp;

                /**
                 * city_name相同
                 */
                return o1.Time.compareTo(o2.Time);
            }
        });
    }

    static void GetDataFromExcel(String excel_path, FIRST_LINE first_line, List<Excel_地区数据> excel_list) throws IOException {
        Workbook workbook = WorkbookFactory.create(new FileInputStream(excel_path));
        Sheet sheet = workbook.getSheetAt(0);

        int cell_len = sheet.getRow(0).getLastCellNum(); //从1开始计数
        int row_len = sheet.getLastRowNum()+1;//从0开始计数

        /**
         * 提取第一行的数据
         */
        Row first_row = sheet.getRow(0);
        first_line.setA1(first_row.getCell(0).getStringCellValue());
        first_line.setB1(first_row.getCell(1).getStringCellValue());
        List<String> first_list_others = new ArrayList<>();
        for (int i = 2; i < cell_len ; i++) {
            first_list_others.add(first_row.getCell(i).getStringCellValue());
        }
        first_line.setOthers(first_list_others);

        /**
         * 提取表格数据
         */
        for (int i = 1; i < row_len ; i++) {
            Row row = sheet.getRow(i);
            List<String> data_list = new ArrayList<>();

            for (int j = 2; j < cell_len; j++) {
                String data_str = "";
                try{
                    data_str = row.getCell(j).getStringCellValue();
                }catch (Exception e){
                    System.out.println("没数据");
                }finally {
                    data_list.add(data_str);
                }
            }

            Excel_地区数据 excel_item = new Excel_地区数据(
                row.getCell(0).getStringCellValue(),
                row.getCell(1).getStringCellValue(),
                data_list
            );

            excel_list.add(excel_item);
        }

        workbook.close();

        logger.info(excel_path + " 读取完毕");
    }
}

class FIRST_LINE{
    String A1, B1;
    List<String> others;

    public FIRST_LINE(){
    }

    public FIRST_LINE(String A1, String B1, List<String> others){
        this.A1 = A1;
        this.B1 = B1;
        this.others = others;
    }

    public void setA1(String a1) {
        A1 = a1;
    }

    public void setB1(String b1) {
        B1 = b1;
    }

    public void setOthers(List<String> others) {
        this.others = others;
    }
}
